开票
SELECT o.ad_client_id,
o.ad_org_id,
o.c_bpartner_id,
o.c_order_id,
o.documentno,
o.dateordered,
o.c_doctype_id,
SUM((l.qtyordered - l.qtyinvoiced) * l.priceactual) AS totallines
FROM c_order o
join c_orderline l ON o.c_order_id = l.c_order_id
join c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_id
left join c_invoiceschedule si ON bp.c_invoiceschedule_id = si.c_invoiceschedule_id
WHERE o.ad_client_id = 1000007
AND ( /*订单状态=完成、关闭、进行中*/
o.docstatus = ANY (array['CO', 'CL', 'IP']))
AND ( /*销售订单单据类型不等于“报价方案”“报价单”“POS订单”*/
o.c_doctype_id IN (
SELECT c_doctype.c_doctype_id
FROM c_doctype
WHERE c_doctype.docbasetype = 'SOO'
AND (
c_doctype.docsubtypeso <> ALL (array['ON', 'OB', 'WR'])))
)
/*销售数量 不等于 开票数量 */
AND l.qtyordered <> l.qtyinvoiced
AND ( /*订单的开票规则= 立即开票*/
o.invoicerule = 'I'
/*订单的开票规则= 按订单开票，且订单行的发货数量等于销售数量 （订单发完货再开票）*/
OR o.invoicerule = 'O'
AND NOT (
EXISTS (
SELECT 1
FROM c_orderline zz1
WHERE zz1.c_order_id = o.c_order_id
AND zz1.qtyordered <> zz1.qtydelivered
)
)
/*订单的开票规则= 按发货开票，且订单行的发货数量不等于开票数量 */
OR o.invoicerule = 'D' AND l.qtyinvoiced <> l.qtydelivered
/*订单的开票规则= 按规则开票，且业务伙伴的开票规则为空 */
OR o.invoicerule = 'S' AND bp.c_invoiceschedule_id IS NULL
/*订单的开票规则= 按规则开票，且业务伙伴的开票规则为非空 */
OR o.invoicerule = 'S' AND bp.c_invoiceschedule_id IS NOT NULL
AND (
/*开票规则的频度为空、或者为“天”、或者为“周” */
si.invoicefrequency IS NULL
OR si.invoicefrequency = 'D'
OR si.invoicefrequency = 'W'
/*开票规则的频度为“半月” */
OR si.invoicefrequency = 'T'
AND (
/*订单的销售日期 <= 当前月+截止日 并且 开票的当前日期 >= 订单月+开票日 */
trunc(o.dateordered) <= (firstof(getdate(), 'MM') + si.invoicedaycutoff - 1)
AND trunc(getdate()) >= (firstof(o.dateordered, 'MM') + si.invoiceday - 1)
/* 或者 订单的销售日期 <= 当前月+截止日+14 并且 开票的当前日期 >= 订单月+开票日+14 */
OR trunc(o.dateordered) <= (firstof(getdate(), 'MM') + si.invoicedaycutoff + 14)
AND trunc(getdate()) >= (firstof(o.dateordered, 'MM') + si.invoiceday + 14 )
)
/*开票规则的频度为“每月” */
OR si.invoicefrequency = 'M'
/*订单的销售日期 <= 当前月+截止日 并且 开票的当前日期 >= 订单月+开票日 */
AND trunc(o.dateordered) <= (firstof(getdate(), 'MM') + si.invoicedaycutoff - 1)
AND trunc(getdate()) >= (firstof(o.dateordered, 'MM') + si.invoiceday - 1)
/*按开票规则开票，筛选到目标订单后，按订单开票（按销售数量全开） */
)
/*只改这里，就可以将“销售订单日期”为“发货单日期”，符合结算习惯 */
/* 一个销售订单可能有多个发货单，有的发货单符合开票规则，有的发货单不符合
那么该订单还是应该被筛选出开票，只对符合时间要求的发货单进行开票 */
)
GROUP BY o.ad_client_id,
o.ad_org_id,
o.c_bpartner_id,
o.c_order_id,
o.documentno,
o.dateordered,
o.c_doctype_id; /**
* InvoiceGenerate InvoiceGenerate.java
* (org.adempiere.base.process\src\org\compiere\process)
* L200
*
* Generate Shipments （备注有错，应该是 Invoice）
* @param pstmt order query
* @return info
*/
private String generate (PreparedStatement pstmt)
{
ResultSet rs = null;
try
{
rs = pstmt.executeQuery ();
while (rs.next ())
{
p_MinimumAmtInvSched = null;
MOrder order = new MOrder (getCtx(), rs, get_TrxName());
StringBuilder msgsup = new StringBuilder(Msg.getMsg(getCtx(), "Processing")).append(" ").append(order.getDocumentInfo());
statusUpdate(msgsup.toString());
// New Invoice Location
if (!p_ConsolidateDocument
|| (m_invoice != null
&& m_invoice.getC_BPartner_Location_ID() != order.getBill_Location_ID()) )
completeInvoice();
boolean completeOrder = MOrder.INVOICERULE_AfterOrderDelivered.equals(order.getInvoiceRule());
// Schedule After Delivery
boolean doInvoice = false;
if (MOrder.INVOICERULE_CustomerScheduleAfterDelivery.equals(order.getInvoiceRule()))
{
m_bp = new MBPartner (getCtx(), order.getBill_BPartner_ID(), null);
if (m_bp.getC_InvoiceSchedule_ID() == 0)
{
log.warning("BPartner has no Schedule - set to After Delivery");
order.setInvoiceRule(MOrder.INVOICERULE_AfterDelivery);
order.saveEx();
}
else
{
MInvoiceSchedule is = MInvoiceSchedule.get(getCtx(), m_bp.getC_InvoiceSchedule_ID(), get_TrxName());
if (is.canInvoice(order.getDateOrdered())) {
if (is.isAmount() && is.getAmt() != null)
p_MinimumAmtInvSched = is.getAmt();
doInvoice = true;
} else {
continue;
}
}
} // Schedule
// After Delivery
if (doInvoice || MOrder.INVOICERULE_AfterDelivery.equals(order.getInvoiceRule()))
{
MInOut[] shipments = order.getShipments();
for (int i = 0; i < shipments.length; i++)
{
MInOut ship = shipments[i];
if (!ship.isComplete() // ignore incomplete or reversals
|| ship.getDocStatus().equals(MInOut.DOCSTATUS_Reversed))
continue;
MInOutLine[] shipLines = ship.getLines(false);
for (int j = 0; j < shipLines.length; j++)
{
MInOutLine shipLine = shipLines[j];
if (!order.isOrderLine(shipLine.getC_OrderLine_ID()))
continue;
if (!shipLine.isInvoiced())
createLine (order, ship, shipLine);
}
m_line += 1000;
}
}
// After Order Delivered, Immediate
else
{
MOrderLine[] oLines = order.getLines(true, null);
for (int i = 0; i < oLines.length; i++)
{
MOrderLine oLine = oLines[i];
BigDecimal toInvoice = oLine.getQtyOrdered().subtract(oLine.getQtyInvoiced());
if (toInvoice.compareTo(Env.ZERO) == 0 && oLine.getM_Product_ID() != 0)
continue;
@SuppressWarnings("unused")
BigDecimal notInvoicedShipment = oLine.getQtyDelivered().subtract(oLine.getQtyInvoiced());
//
boolean fullyDelivered = oLine.getQtyOrdered().compareTo(oLine.getQtyDelivered()) == 0;
// Complete Order
if (completeOrder && !fullyDelivered)
{
if (log.isLoggable(Level.FINE)) log.fine("Failed CompleteOrder - " + oLine);
addBufferLog(0, null, null,"Failed CompleteOrder - " + oLine,oLine.get_Table_ID(),oLine.getC_OrderLine_ID()); // Elaine 2008/11/25
completeOrder = false;
break;
}
// Immediate
else if (MOrder.INVOICERULE_Immediate.equals(order.getInvoiceRule()))
{
if (log.isLoggable(Level.FINE)) log.fine("Immediate - ToInvoice=" + toInvoice + " - " + oLine);
BigDecimal qtyEntered = toInvoice;
// Correct UOM for QtyEntered
if (oLine.getQtyEntered().compareTo(oLine.getQtyOrdered()) != 0)
qtyEntered = toInvoice
.multiply(oLine.getQtyEntered())
.divide(oLine.getQtyOrdered(), 12, BigDecimal.ROUND_HALF_UP);
createLine (order, oLine, toInvoice, qtyEntered);
}
else if (!completeOrder)
{
if (log.isLoggable(Level.FINE)) log.fine("Failed: " + order.getInvoiceRule()
+ " - ToInvoice=" + toInvoice + " - " + oLine);
addBufferLog(0, null, null,"Failed: " + order.getInvoiceRule()
+ " - ToInvoice=" + toInvoice + " - " + oLine,oLine.get_Table_ID(),oLine.getC_OrderLine_ID());
}
} // for all order lines
if (MOrder.INVOICERULE_Immediate.equals(order.getInvoiceRule()))
m_line += 1000;
}
// Complete Order successful
if (completeOrder && MOrder.INVOICERULE_AfterOrderDelivered.equals(order.getInvoiceRule()))
{
MInOut[] shipments = order.getShipments();
for (int i = 0; i < shipments.length; i++)
{
MInOut ship = shipments[i];
if (!ship.isComplete() // ignore incomplete or reversals
|| ship.getDocStatus().equals(MInOut.DOCSTATUS_Reversed))
continue;
MInOutLine[] shipLines = ship.getLines(false);
for (int j = 0; j < shipLines.length; j++)
{
MInOutLine shipLine = shipLines[j];
if (!order.isOrderLine(shipLine.getC_OrderLine_ID()))
continue;
if (!shipLine.isInvoiced())
createLine (order, ship, shipLine);
}
m_line += 1000;
}
} // complete Order
} // for all orders
}
catch (Exception e)
{
throw new AdempiereException(e);
}
finally
{
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
completeInvoice();
StringBuilder msgreturn = new StringBuilder("@Created@ = ").append(m_created);
return msgreturn.toString();
} // generate